"Execute any procedure" grant?

[Mark DeWaard]

I am trying to set up a security system for my senior developers where they automatically can execute any procedure. It is in the System Administrator server role. Is there any way I can grant this role to their database role withour giving them complete System Adminstrator rights?

[Chris Thibodeaux]

Alias them as DBO on the Database.


------------
Mark DeWaard at 2/9/01 11:53:15 AM

I am trying to set up a security system for my senior developers where they automatically can execute any procedure. It is in the System Administrator server role. Is there any way I can grant this role to their database role withour giving them complete System Adminstrator rights?

[Mark DeWaard]

Thank you for your response.

Do you mean use sp_addalias <user_name> <alias_login_name>.

Does this allow the user to have all dbo rights? If so, wouldn&#39;t it be the same as as granting the user system_adminstrator roles. (we are using version 7.0 and the BOL indicate that it is only used for backwards compatibility)

I guess what I&#39;mm looking for is the specific role that allows the user to execute any procedure. I know it is in oracle, I cannot find anything similar in MS SQL. I don&#39;t want to give them the rights do DBA type activities, but allow them to execute all the stored procedures without specifically granting them rights to each one.


------------
Chris Thibodeaux at 2/9/01 12:48:19 PM

Alias them as DBO on the Database.


------------
Mark DeWaard at 2/9/01 11:53:15 AM

I am trying to set up a security system for my senior developers where they automatically can execute any procedure. It is in the System Administrator server role. Is there any way I can grant this role to their database role withour giving them complete System Adminstrator rights?

[Phil McCormack]

How about creating a DB role within you DB of Exec_AllProcs.

you can then grant exec all procs to the role and then grant role to users/group.

Some SQL to ease the pain would like :-

sp_addrole &#39;Exec_AllProcs&#39;
go
select &#39;grant execute on &#39;+name+&#39; to Exec_AllProcs&#39;
from sysobjects
where type=&#39;P&#39;
order by name

Then using EM, give the users/group the role of Exec_AllProcs.

When adding new procs, make sure to re-run the above (minus the addrole)


------------
Mark DeWaard at 2/12/01 5:35:28 PM

Thank you for your response.

Do you mean use sp_addalias <user_name> <alias_login_name>.

Does this allow the user to have all dbo rights? If so, wouldn&#39;t it be the same as as granting the user system_adminstrator roles. (we are using version 7.0 and the BOL indicate that it is only used for backwards compatibility)

I guess what I&#39;mm looking for is the specific role that allows the user to execute any procedure. I know it is in oracle, I cannot find anything similar in MS SQL. I don&#39;t want to give them the rights do DBA type activities, but allow them to execute all the stored procedures without specifically granting them rights to each one.


------------
Chris Thibodeaux at 2/9/01 12:48:19 PM

Alias them as DBO on the Database.


------------
Mark DeWaard at 2/9/01 11:53:15 AM

I am trying to set up a security system for my senior developers where they automatically can execute any procedure. It is in the System Administrator server role. Is there any way I can grant this role to their database role withour giving them complete System Adminstrator rights?